<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>初探 MySQL | YoiaL</title><meta name="keywords" content="学习笔记,菜鸟入门,MySQL"><meta name="author" content="John Doe"><meta name="copyright" content="John Doe"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="SQL 语言功能 一、 数据定义语言 create   创建alter    修改orop     删除 二、数据查询语言 select   检索 三、数据操纵语言 insert   插入update   更新delete   删除 四、数据控制语言 grant    授予权限revoke   收回权限 创建和管理数据库 创建数据库 命名规则 1、任意字母，数字下划线，$组成 2、不能使用单独数字">
<meta property="og:type" content="article">
<meta property="og:title" content="初探 MySQL">
<meta property="og:url" content="https://liaoyia.gitee.io/2020/03/22/MySQL%E5%88%9B%E5%BB%BA%E5%92%8C%E7%AE%A1%E7%90%86%E6%95%B0%E6%8D%AE%E5%BA%93/index.html">
<meta property="og:site_name" content="YoiaL">
<meta property="og:description" content="SQL 语言功能 一、 数据定义语言 create   创建alter    修改orop     删除 二、数据查询语言 select   检索 三、数据操纵语言 insert   插入update   更新delete   删除 四、数据控制语言 grant    授予权限revoke   收回权限 创建和管理数据库 创建数据库 命名规则 1、任意字母，数字下划线，$组成 2、不能使用单独数字">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png">
<meta property="article:published_time" content="2020-03-22T05:08:11.000Z">
<meta property="article:modified_time" content="2021-03-01T09:04:14.916Z">
<meta property="article:author" content="John Doe">
<meta property="article:tag" content="学习笔记">
<meta property="article:tag" content="菜鸟入门">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png"><link rel="shortcut icon" href="/liaoyia/img/favicon.png"><link rel="canonical" href="https://liaoyia.gitee.io/2020/03/22/MySQL%E5%88%9B%E5%BB%BA%E5%92%8C%E7%AE%A1%E7%90%86%E6%95%B0%E6%8D%AE%E5%BA%93/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/liaoyia/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/liaoyia/',
  algolia: undefined,
  localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
  translate: {"defaultEncoding":1,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: true,
    post: true
  },
  runtime: '',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"bottom-center"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: true,
  islazyload: false,
  isanchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-03-01 17:04:14'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    })(window)</script><link rel="stylesheet" href="/css/fishes.css"><style type="text/css">#toggle-sidebar { left : 100px }</style><meta name="generator" content="Hexo 5.4.0"></head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="/liaoyia/null" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/liaoyia/archives/"><div class="headline">文章</div><div class="length-num">16</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/liaoyia/tags/"><div class="headline">标签</div><div class="length-num">9</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/liaoyia/categories/"><div class="headline">分类</div><div class="length-num">6</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/liaoyia/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fa-fw fas fa-compass"></i><span> 目录</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/liaoyia/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page" href="/liaoyia/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page" href="/liaoyia/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fa-fw fa fa-heartbeat"></i><span> 娱乐</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/liaoyia/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page" href="/liaoyia/movies/"><i class="fa-fw fas fa-video"></i><span> 电影</span></a></li><li><a class="site-page" href="/liaoyia/books/"><i class="fa-fw fa fa-book"></i><span> 书籍</span></a></li><li><a class="site-page" href="/liaoyia/games/"><i class="fa-fw fa fa-gamepad"></i><span> 游戏</span></a></li><li><a class="site-page" href="/liaoyia/ziyu/"><i class="fa-fw fa-fw fas fa-cubes"></i><span> 自言自语</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw laptop-code"></i><span> 🎯</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/liaoyia/messages/"><i class="fa-fw fa-fw fas fa-comment-dots"></i><span> 留言板</span></a></li><li><a class="site-page" href="/liaoyia/link/"><i class="fa-fw fas fa-link"></i><span> Link</span></a></li><li><a class="site-page" href="/liaoyia/about/"><i class="fa-fw fa-fw fas fa-heart"></i><span> 关于我</span></a></li></ul></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/liaoyia/">YoiaL</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/liaoyia/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fa-fw fas fa-compass"></i><span> 目录</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/liaoyia/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page" href="/liaoyia/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page" href="/liaoyia/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fa-fw fa fa-heartbeat"></i><span> 娱乐</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/liaoyia/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page" href="/liaoyia/movies/"><i class="fa-fw fas fa-video"></i><span> 电影</span></a></li><li><a class="site-page" href="/liaoyia/books/"><i class="fa-fw fa fa-book"></i><span> 书籍</span></a></li><li><a class="site-page" href="/liaoyia/games/"><i class="fa-fw fa fa-gamepad"></i><span> 游戏</span></a></li><li><a class="site-page" href="/liaoyia/ziyu/"><i class="fa-fw fa-fw fas fa-cubes"></i><span> 自言自语</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw laptop-code"></i><span> 🎯</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page" href="/liaoyia/messages/"><i class="fa-fw fa-fw fas fa-comment-dots"></i><span> 留言板</span></a></li><li><a class="site-page" href="/liaoyia/link/"><i class="fa-fw fas fa-link"></i><span> Link</span></a></li><li><a class="site-page" href="/liaoyia/about/"><i class="fa-fw fa-fw fas fa-heart"></i><span> 关于我</span></a></li></ul></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">初探 MySQL</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2020-03-22T05:08:11.000Z" title="发表于 2020-03-22 13:08:11">2020-03-22</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-03-01T09:04:14.916Z" title="更新于 2021-03-01 17:04:14">2021-03-01</time></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">833</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>3分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/hint.css/2.4.1/hint.min.css"><h2 id="SQL-语言功能">SQL 语言功能</h2>
<h3 id="一、-数据定义语言">一、 数据定义语言</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">create   创建</span><br><span class="line">alter    修改</span><br><span class="line">orop     删除</span><br></pre></td></tr></table></figure>
<h3 id="二、数据查询语言">二、数据查询语言</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">select   检索</span><br></pre></td></tr></table></figure>
<h3 id="三、数据操纵语言">三、数据操纵语言</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">insert   插入</span><br><span class="line">update   更新</span><br><span class="line">delete   删除</span><br></pre></td></tr></table></figure>
<h3 id="四、数据控制语言">四、数据控制语言</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">grant    授予权限</span><br><span class="line">revoke   收回权限</span><br></pre></td></tr></table></figure>
<h1>创建和管理数据库</h1>
<h3 id="创建数据库">创建数据库</h3>
<h3 id="命名规则">命名规则</h3>
<p><code>1、任意字母，数字下划线，$组成</code><br>
<code>2、不能使用单独数字，不能重名，不能使用MySQL关键字作为数据库名。</code></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">create database 数据库名;</span><br><span class="line"></span><br><span class="line">create database study;   &#x2F;&#x2F;创建一个名为study的数据库</span><br></pre></td></tr></table></figure>
<h3 id="查看已创建和修改字符编码格式。">查看已创建和修改字符编码格式。</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">show databases;    &#x2F;&#x2F;查看所有已创建数据库</span><br><span class="line"></span><br><span class="line">show variables like &#39;datadir&#39;; 	    &#x2F;&#x2F;查看安装目录</span><br><span class="line"></span><br><span class="line">show variables like &#39;character%&#39;;   &#x2F;&#x2F;查看当前数据库默认字符编码格式</span><br><span class="line"></span><br><span class="line">show create database 数据库名;  	   &#x2F;&#x2F;查看指定数据库信息</span><br><span class="line"></span><br><span class="line">alter database study default character set utf8;    &#x2F;&#x2F;修改study数据库字符编码格式为utf8。</span><br><span class="line"></span><br><span class="line">drop database study;  &#x2F;&#x2F;删除study数据库（无提示，谨慎使用！）</span><br></pre></td></tr></table></figure>
<h1>创建和管理数据表</h1>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">use 数据库名   &#x2F;&#x2F;选择数据库</span><br></pre></td></tr></table></figure>
<h3 id="创建数据表">创建数据表</h3>
<p><code>字段名不能重复，建议使用英文，有可读性易懂</code></p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">create table  表名</span><br><span class="line">(字段名1 数据类型[约束条件],  &#x2F;&#x2F;约束条件为可选项</span><br><span class="line"> 字段名2 数据类型[约束条件],</span><br><span class="line"> ......</span><br><span class="line"> 字段名n 数据类型[约束条件]</span><br><span class="line">	);</span><br><span class="line"></span><br><span class="line">&#x2F;&#x2F;例如创建一个名为book的数据表并添加b_id,b_name,b_time字段</span><br><span class="line">create table  book</span><br><span class="line">(b_id char(7) primary key,      &#x2F;&#x2F;设置主键</span><br><span class="line"> b_name varchar(30) not null,   &#x2F;&#x2F;设置非空</span><br><span class="line"> b_time date comment &#39;出版时间&#39;  &#x2F;&#x2F;备注说明</span><br><span class="line">	);</span><br></pre></td></tr></table></figure>
<h3 id="一些常用命令">一些常用命令</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">source C:&#x2F;**.txt;       &#x2F;&#x2F;从本地TXT文件导入数据</span><br><span class="line"></span><br><span class="line">show tables;            &#x2F;&#x2F;查看所有表</span><br><span class="line"></span><br><span class="line">show create table 表名  &#x2F;&#x2F;查看表信息 (定义语句、字符编码)</span><br><span class="line"></span><br><span class="line">desc 表名;             &#x2F;&#x2F;查看表字段信息(字段名、字段类型等)</span><br></pre></td></tr></table></figure>
<h4 id="表说明">表说明</h4>
<p><img src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-ly0022.png" alt=""></p>
<table>
<thead>
<tr>
<th>字段</th>
<th>解释</th>
</tr>
</thead>
<tbody>
<tr>
<td>Field</td>
<td>字段名 Type: 字段类型。</td>
</tr>
<tr>
<td>NULL</td>
<td>表示该列是否可以存储 NULL 值。</td>
</tr>
<tr>
<td>Key</td>
<td>表示该列是否已经有索引。</td>
</tr>
<tr>
<td>Default</td>
<td>表示该列是否有默认值。</td>
</tr>
<tr>
<td>Extra</td>
<td>表示获取到的与给定列相关的附加信息。</td>
</tr>
</tbody>
</table>
<h3 id="复制表">复制表</h3>
<h4 id="1-新表和参照表结构相同，但新表中没有数据，是张-空表”。">1.新表和参照表结构相同，但新表中没有数据，是张&quot;空表”。</h4>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">create table 新表名 like 参照表名;</span><br><span class="line"></span><br><span class="line">[例] 创建member表的一个名为copy1的拷贝。</span><br><span class="line"></span><br><span class="line">create table copy1 like member;</span><br></pre></td></tr></table></figure>
<h4 id="2-将参照表中的数据和结构一同复制到新表，但约束不能复制。">2.将参照表中的数据和结构一同复制到新表，但约束不能复制。</h4>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">create table 新表名 as  (查询语句) ;</span><br><span class="line"></span><br><span class="line">[例] 创建表member的一个名为copy2的拷贝，并且复制其内容。</span><br><span class="line"></span><br><span class="line">create table copy2</span><br><span class="line">as (select * from member);</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="修改表名">修改表名</h3>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">alter table 旧表名 rename 新表名;</span><br><span class="line">&#x2F;&#x2F;或者</span><br><span class="line">rename table 旧表名 to 新表名;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="表中字段的增删改">表中字段的增删改</h3>
<p>[例] 把 member 表中 m_pw 字段名改为 m_mm,数据类型不变。</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">after table member change</span><br><span class="line">m_pw m_mm char(6);</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>[例] 把 member 表中 m_time 字段类型由 data 改为 datetime。</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">after table member modify</span><br><span class="line">m_time datetime;</span><br></pre></td></tr></table></figure>
<p>[例]将 member 表中的 m<em>name 字段后添加一个新的年龄字段 m</em> age,类型为 tinyint unsigned,无约束条件。</p>
<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line">alter table member</span><br><span class="line"></span><br><span class="line">add m_ age tinyint unsigned</span><br><span class="line"></span><br><span class="line">after m_name ;  &#x2F;&#x2F; 用first可放表中第一个字段</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">&#x2F;&#x2F;删除表</span><br><span class="line">deop table 表名1,表名2.....;  &#x2F;&#x2F;只能删除未被关联表！</span><br></pre></td></tr></table></figure>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:null">John Doe</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://liaoyia.gitee.io/2020/03/22/MySQL%E5%88%9B%E5%BB%BA%E5%92%8C%E7%AE%A1%E7%90%86%E6%95%B0%E6%8D%AE%E5%BA%93/">https://liaoyia.gitee.io/2020/03/22/MySQL%E5%88%9B%E5%BB%BA%E5%92%8C%E7%AE%A1%E7%90%86%E6%95%B0%E6%8D%AE%E5%BA%93/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://liaoyia.gitee.io" target="_blank">YoiaL</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/liaoyia/tags/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/">学习笔记</a><a class="post-meta__tags" href="/liaoyia/tags/%E8%8F%9C%E9%B8%9F%E5%85%A5%E9%97%A8/">菜鸟入门</a><a class="post-meta__tags" href="/liaoyia/tags/MySQL/">MySQL</a></div><div class="post_share"><div class="social-share" data-image="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/liaoyia/2020/04/06/MySQL%E6%95%B0%E6%8D%AE%E5%AE%8C%E6%95%B4%E6%80%A7%E7%BA%A6%E6%9D%9F/"><img class="prev-cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lysel.jpg" onerror="onerror=null;src='/liaoyia/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">MySQL数据完整性约束</div></div></a></div><div class="next-post pull-right"><a href="/liaoyia/2019/12/27/Linux-sed/"><img class="next-cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lyphotosnn3.png" onerror="onerror=null;src='/liaoyia/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">Linux 三剑客 </div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/liaoyia/2020/04/29/MySQL表查询实训笔记/" title="MySQL表查询实训"><img class="cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-04-29</div><div class="title">MySQL表查询实训</div></div></a></div><div><a href="/liaoyia/2020/04/09/MySQL数据更新/" title="MySQL数据更新和单表查询"><img class="cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lysee.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-04-09</div><div class="title">MySQL数据更新和单表查询</div></div></a></div><div><a href="/liaoyia/2020/04/06/MySQL数据完整性约束/" title="MySQL数据完整性约束"><img class="cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lysel.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-04-06</div><div class="title">MySQL数据完整性约束</div></div></a></div><div><a href="/liaoyia/2020/06/26/MySQL基础/" title="Mysql回顾"><img class="cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymysql.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-06-26</div><div class="title">Mysql回顾</div></div></a></div><div><a href="/liaoyia/2020/06/22/css层叠样式表/" title="Css层叠样式表期末复习"><img class="cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lyHTML.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-06-22</div><div class="title">Css层叠样式表期末复习</div></div></a></div><div><a href="/liaoyia/2020/04/08/Markdown/" title="Markdown基本语法总结"><img class="cover" src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymd.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-04-08</div><div class="title">Markdown基本语法总结</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="/liaoyia/null" onerror="this.onerror=null;this.src='/liaoyia/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">John Doe</div><div class="author-info__description"></div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/liaoyia/archives/"><div class="headline">文章</div><div class="length-num">16</div></a></div><div class="card-info-data-item is-center"><a href="/liaoyia/tags/"><div class="headline">标签</div><div class="length-num">9</div></a></div><div class="card-info-data-item is-center"><a href="/liaoyia/categories/"><div class="headline">分类</div><div class="length-num">6</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://github.com/liaoyio" target="_blank" title="Github"><i class="fab fa-github"></i></a><a class="social-icon" href="/liaoyia/2417276459" target="_blank" title="QQ"><i class="fas fa-qq"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">This is my Blog</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#SQL-%E8%AF%AD%E8%A8%80%E5%8A%9F%E8%83%BD"><span class="toc-number">1.</span> <span class="toc-text">SQL 语言功能</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%80%E3%80%81-%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80"><span class="toc-number">1.1.</span> <span class="toc-text">一、 数据定义语言</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2%E8%AF%AD%E8%A8%80"><span class="toc-number">1.2.</span> <span class="toc-text">二、数据查询语言</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%89%E3%80%81%E6%95%B0%E6%8D%AE%E6%93%8D%E7%BA%B5%E8%AF%AD%E8%A8%80"><span class="toc-number">1.3.</span> <span class="toc-text">三、数据操纵语言</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%9B%9B%E3%80%81%E6%95%B0%E6%8D%AE%E6%8E%A7%E5%88%B6%E8%AF%AD%E8%A8%80"><span class="toc-number">1.4.</span> <span class="toc-text">四、数据控制语言</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number"></span> <span class="toc-text">创建和管理数据库</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">0.1.</span> <span class="toc-text">创建数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%91%BD%E5%90%8D%E8%A7%84%E5%88%99"><span class="toc-number">0.2.</span> <span class="toc-text">命名规则</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E7%9C%8B%E5%B7%B2%E5%88%9B%E5%BB%BA%E5%92%8C%E4%BF%AE%E6%94%B9%E5%AD%97%E7%AC%A6%E7%BC%96%E7%A0%81%E6%A0%BC%E5%BC%8F%E3%80%82"><span class="toc-number">0.3.</span> <span class="toc-text">查看已创建和修改字符编码格式。</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number"></span> <span class="toc-text">创建和管理数据表</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E8%A1%A8"><span class="toc-number">0.1.</span> <span class="toc-text">创建数据表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%80%E4%BA%9B%E5%B8%B8%E7%94%A8%E5%91%BD%E4%BB%A4"><span class="toc-number">0.2.</span> <span class="toc-text">一些常用命令</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%A1%A8%E8%AF%B4%E6%98%8E"><span class="toc-number">0.2.1.</span> <span class="toc-text">表说明</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%A4%8D%E5%88%B6%E8%A1%A8"><span class="toc-number">0.3.</span> <span class="toc-text">复制表</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#1-%E6%96%B0%E8%A1%A8%E5%92%8C%E5%8F%82%E7%85%A7%E8%A1%A8%E7%BB%93%E6%9E%84%E7%9B%B8%E5%90%8C%EF%BC%8C%E4%BD%86%E6%96%B0%E8%A1%A8%E4%B8%AD%E6%B2%A1%E6%9C%89%E6%95%B0%E6%8D%AE%EF%BC%8C%E6%98%AF%E5%BC%A0-%E7%A9%BA%E8%A1%A8%E2%80%9D%E3%80%82"><span class="toc-number">0.3.1.</span> <span class="toc-text">1.新表和参照表结构相同，但新表中没有数据，是张&quot;空表”。</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-%E5%B0%86%E5%8F%82%E7%85%A7%E8%A1%A8%E4%B8%AD%E7%9A%84%E6%95%B0%E6%8D%AE%E5%92%8C%E7%BB%93%E6%9E%84%E4%B8%80%E5%90%8C%E5%A4%8D%E5%88%B6%E5%88%B0%E6%96%B0%E8%A1%A8%EF%BC%8C%E4%BD%86%E7%BA%A6%E6%9D%9F%E4%B8%8D%E8%83%BD%E5%A4%8D%E5%88%B6%E3%80%82"><span class="toc-number">0.3.2.</span> <span class="toc-text">2.将参照表中的数据和结构一同复制到新表，但约束不能复制。</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E8%A1%A8%E5%90%8D"><span class="toc-number">0.4.</span> <span class="toc-text">修改表名</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%A1%A8%E4%B8%AD%E5%AD%97%E6%AE%B5%E7%9A%84%E5%A2%9E%E5%88%A0%E6%94%B9"><span class="toc-number">0.5.</span> <span class="toc-text">表中字段的增删改</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/liaoyia/2020/11/12/hello-world/" title="Hello World"><img src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/img/default.jpg" onerror="this.onerror=null;this.src='/liaoyia/img/404.jpg'" alt="Hello World"/></a><div class="content"><a class="title" href="/liaoyia/2020/11/12/hello-world/" title="Hello World">Hello World</a><time datetime="2020-11-12T05:23:35.000Z" title="发表于 2020-11-12 13:23:35">2020-11-12</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/liaoyia/2020/06/26/MySQL%E5%9F%BA%E7%A1%80/" title="Mysql回顾"><img src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymysql.jpg" onerror="this.onerror=null;this.src='/liaoyia/img/404.jpg'" alt="Mysql回顾"/></a><div class="content"><a class="title" href="/liaoyia/2020/06/26/MySQL%E5%9F%BA%E7%A1%80/" title="Mysql回顾">Mysql回顾</a><time datetime="2020-06-26T12:34:53.000Z" title="发表于 2020-06-26 20:34:53">2020-06-26</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/liaoyia/2020/06/22/css%E5%B1%82%E5%8F%A0%E6%A0%B7%E5%BC%8F%E8%A1%A8/" title="Css层叠样式表期末复习"><img src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lyHTML.png" onerror="this.onerror=null;this.src='/liaoyia/img/404.jpg'" alt="Css层叠样式表期末复习"/></a><div class="content"><a class="title" href="/liaoyia/2020/06/22/css%E5%B1%82%E5%8F%A0%E6%A0%B7%E5%BC%8F%E8%A1%A8/" title="Css层叠样式表期末复习">Css层叠样式表期末复习</a><time datetime="2020-06-22T11:08:56.000Z" title="发表于 2020-06-22 19:08:56">2020-06-22</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/liaoyia/2020/04/29/MySQL%E8%A1%A8%E6%9F%A5%E8%AF%A2%E5%AE%9E%E8%AE%AD%E7%AC%94%E8%AE%B0/" title="MySQL表查询实训"><img src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png" onerror="this.onerror=null;this.src='/liaoyia/img/404.jpg'" alt="MySQL表查询实训"/></a><div class="content"><a class="title" href="/liaoyia/2020/04/29/MySQL%E8%A1%A8%E6%9F%A5%E8%AF%A2%E5%AE%9E%E8%AE%AD%E7%AC%94%E8%AE%B0/" title="MySQL表查询实训">MySQL表查询实训</a><time datetime="2020-04-29T08:21:56.000Z" title="发表于 2020-04-29 16:21:56">2020-04-29</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/liaoyia/2020/04/09/MySQL%E6%95%B0%E6%8D%AE%E6%9B%B4%E6%96%B0/" title="MySQL数据更新和单表查询"><img src="https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lysee.png" onerror="this.onerror=null;this.src='/liaoyia/img/404.jpg'" alt="MySQL数据更新和单表查询"/></a><div class="content"><a class="title" href="/liaoyia/2020/04/09/MySQL%E6%95%B0%E6%8D%AE%E6%9B%B4%E6%96%B0/" title="MySQL数据更新和单表查询">MySQL数据更新和单表查询</a><time datetime="2020-04-09T08:08:56.000Z" title="发表于 2020-04-09 16:08:56">2020-04-09</time></div></div></div></div></div></div></main><footer id="footer" style="background-image: url('https://cdn.jsdelivr.net/gh/liaoyio/imgHosting/img-lymonvpng.png')"><div id="footer-wrap"><div class="copyright">&copy;2018 - 2021 <i style="color:#FF6A6A" class="fa fa-heartbeat"></i> John Doe</div><div class="footer_custom_text">Hi, welcome to my <a target="_blank" rel="noopener" href="https://liaoyi.xyz/">blog</a>!</div></div><div class="container" id="jsi-flying-fish-container"></div><script src="js/fish.js"></script><style>@media only screen and (max-width: 767px){
#sidebar_search_box input[type=text]{width:calc(100% - 24px)}
}</style></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="translateLink" type="button" title="简繁转换">繁</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><div class="search-dialog__title" id="local-search-title">本地搜索</div><div id="local-input-panel"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div></div><hr/><div id="local-search-results"></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/liaoyia/js/utils.js"></script><script src="/liaoyia/js/main.js"></script><script src="/liaoyia/js/tw_cn.js"></script><script src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script>function panguFn () {
  if (typeof pangu === 'object') pangu.autoSpacingPage()
  else {
    getScript('https://cdn.jsdelivr.net/npm/pangu/dist/browser/pangu.min.js')
      .then(() => {
        pangu.autoSpacingPage()
      })
  }
}

function panguInit () {
  if (false){
    GLOBAL_CONFIG_SITE.isPost && panguFn()
  } else {
    panguFn()
  }
}

document.addEventListener('DOMContentLoaded', panguInit)</script><script src="/liaoyia/js/search/local-search.js"></script><div class="js-pjax"></div><script src="https://cdn.jsdelivr.net/gh/sviptzk/HexoStaticFile@latest/Hexo/js/mouse_snow.min.js"></script><script src="js/jquery.min.js"></script><script src="/js/fish.js"></script><div class="aplayer no -destroy" data-id="000PeZCQ1i4XVs" data-server="tencent" data-type="artist" data-fixed="true" data-mini=" true" data-listFolded="false" data-order="random" data-preload="none" data-autoplay="true" muted></div><script src="/js/fishes.js"></script><script id="click-heart" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="true"></script><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/aplayer/dist/APlayer.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/aplayer/dist/APlayer.min.js"></script><script src="https://cdn.jsdelivr.net/gh/metowolf/MetingJS@1.2/dist/Meting.min.js"></script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>